#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   dataUpdateTest.py    
@Contact :   pengwei.sun@aihuishou.com
@License :   (C)Copyright aihuishou

@Modify Time      @Author       @Version    @Desciption
------------      -----------   --------    -----------
2021-07-22 17:15   pengwei.sun      1.0         None
'''
import os
import sys
sys.path.append(os.getcwd())
from src.utils.db_processor import mysql_prediction_processor
import pandas as pd
import numpy as np
import time
from src.utils.config import logger

query_sql="""
select distinct price.product_sku_key,price.product_level_key,a.price as price_0_7, a.sale_num as sale_num_0_7,a.sku_cnt,
a.maxprice as maxprice_0_7,
a.minprice as minprice_0_7,
a.sale_num_0_3,a.price_0_3,
b.price as price_8_14,b.sale_num as sale_num_8_14 ,
c.price as price_15_21, c.sale_num as sale_num_15_21 ,
d.price as price_22_42,d.sale_num as sale_num_22_42

from price_prediction_level2_rate_price_brand_tablet_v1 price
left join 

(
    select a.product_id,a.mapping_product_sku_id as product_sku_id,
    a.mapping_product_level_id as product_level_id,
    avg(a.real_sell_price) as price,
    COUNT(1) AS sale_num,
    b.sku_cnt as sku_cnt,
    count(distinct shop_out_date) as cnt_date,max(a.real_sell_price) as maxprice,min(a.real_sell_price) as minprice,
    sum(case when shop_out_date between DATE_FORMAT(date_sub(curdate(),interval 3 day),'%Y%m%d')  and DATE_FORMAT(date_sub(curdate(),interval 0 day),'%Y%m%d') then 1 else 0 end) as sale_num_0_3,
    avg(case when shop_out_date between DATE_FORMAT(date_sub(curdate(),interval 3 day),'%Y%m%d')  and DATE_FORMAT(date_sub(curdate(),interval 0 day),'%Y%m%d') then a.real_sell_price else null end) as price_0_3
    from product_price_info  a
    left join (
        select product_id,mapping_product_sku_id,
        count(distinct mapping_product_level_id) AS cnt,
        count( mapping_product_level_id) AS sku_cnt,
        count(distinct product_level_id_2) AS cnt1
        from
        (
        select distinct a.product_id,a.mapping_product_sku_id ,b.level_name as mapping_product_level_name ,
        a.mapping_product_level_id,
        case when substring(b.level_name,1,1) in ('S','A','B','C') 
        THEN a.mapping_product_level_id ELSE null end product_level_id_2 
         from product_price_info a 
         inner join warehouse.dim_product_level b
         on a.mapping_product_level_id = b.level_id
        where  a.shop_out_date between DATE_FORMAT(date_sub('{}',interval 6 day),'%Y%m%d')  and DATE_FORMAT(date_sub('{}',interval 0 day),'%Y%m%d') 
        and a.product_category_id=6  and a.mapping_product_level_id>0
        and  (a.sale_out_cnt is  null OR a.sale_out_cnt<=1)
        ) a
        GROUP BY 1,2
        having  count(distinct product_level_id_2)>0 and count(distinct mapping_product_level_id)>=0
    )b
    on  a.product_id=b.product_id and a.mapping_product_sku_id=b.mapping_product_sku_id
    
    where a.shop_out_date between DATE_FORMAT(date_sub('{}',interval 6 day),'%Y%m%d')  and DATE_FORMAT(date_sub('{}',interval 0 day),'%Y%m%d')  
     and a.product_category_id=6 
    and  a.product_source_id in (101,103)
    and  (a.sale_out_cnt is  null OR a.sale_out_cnt<=1)
    and a.mapping_product_level_id >0
    GROUP BY 1,2,3
    order by 1,2,3
)a
on price.product_sku_key=a.product_sku_id and price.product_level_key=a.product_level_id

left join 

(
    select a.product_id,a.mapping_product_sku_id as product_sku_id,
    a.mapping_product_level_id as product_level_id,
    avg(a.real_sell_price) as price,
    COUNT(1) AS sale_num,
    b.sku_cnt as sku_cnt,
    count(distinct shop_out_date) as cnt_date,max(a.real_sell_price) as maxprice,min(a.real_sell_price) as minprice
     from product_price_info  a
    left join (
        select product_id,mapping_product_sku_id,
        count(distinct mapping_product_level_id) AS cnt,
        count( mapping_product_level_id) AS sku_cnt,
        count(distinct product_level_id_2) AS cnt1
        from
        (
        select distinct a.product_id,a.mapping_product_sku_id ,b.level_name as mapping_product_level_name ,
        a.mapping_product_level_id,
        case when substring(b.level_name,1,1) in ('S','A','B','C') 
        THEN a.mapping_product_level_id ELSE null end product_level_id_2 
         from product_price_info a 
         inner join warehouse.dim_product_level b
         on a.mapping_product_level_id = b.level_id
        where a.shop_out_date between DATE_FORMAT(date_sub('{}',interval 13 day),'%Y%m%d')  and DATE_FORMAT(date_sub('{}',interval 7 day),'%Y%m%d') 
        and a.product_category_id=6  and a.mapping_product_level_id>0
         and (a.sale_out_cnt is  null OR a.sale_out_cnt<=1)
        ) a
        GROUP BY 1,2
        having  count(distinct product_level_id_2)>0 and count(distinct mapping_product_level_id)>=0
    )b
    on  a.product_id=b.product_id and a.mapping_product_sku_id=b.mapping_product_sku_id
    
    where a.shop_out_date between DATE_FORMAT(date_sub('{}',interval 13 day),'%Y%m%d')  and DATE_FORMAT(date_sub('{}',interval 7 day),'%Y%m%d')  
     and a.product_category_id=6
    and  a.product_source_id in (101,103)
     and (a.sale_out_cnt is  null OR a.sale_out_cnt<=1)
    and a.mapping_product_level_id >0
    GROUP BY 1,2,3
    order by 1,2,3
)b


on price.product_sku_key=b.product_sku_id and price.product_level_key=b.product_level_id
left join
(
    select a.product_id,a.mapping_product_sku_id as product_sku_id,
    a.mapping_product_level_id as product_level_id,
    avg(a.real_sell_price) as price,
    COUNT(1) AS sale_num,
    count(distinct shop_out_date) as cnt_date,max(a.real_sell_price) as maxprice,min(a.real_sell_price) as minprice
     from product_price_info  a
    left join (
        select product_id,mapping_product_sku_id,
        count(distinct mapping_product_level_id) AS cnt,
        count(distinct product_level_id_2) AS cnt1
        from
        (
        select distinct a.product_id,a.mapping_product_sku_id ,b.level_name as mapping_product_level_name ,
        a.mapping_product_level_id,
        case when substring(b.level_name,1,1) in ('S','A','B','C') 
        THEN a.mapping_product_level_id ELSE null end product_level_id_2 
         from product_price_info a 
         inner join warehouse.dim_product_level b
         on a.mapping_product_level_id = b.level_id
        where a.shop_out_date between DATE_FORMAT(date_sub('{}',interval 20 day),'%Y%m%d')  and DATE_FORMAT(date_sub('{}',interval 14 day),'%Y%m%d') 
        and a.product_category_id=6  and a.mapping_product_level_id>0
         and (a.sale_out_cnt is  null OR a.sale_out_cnt<=1)
        ) a
        GROUP BY 1,2
        having  count(distinct product_level_id_2)>0 and count(distinct mapping_product_level_id)>=0
    )b
    on  a.product_id=b.product_id and a.mapping_product_sku_id=b.mapping_product_sku_id
    
    where  a.shop_out_date between DATE_FORMAT(date_sub('{}',interval 20 day),'%Y%m%d')  and DATE_FORMAT(date_sub('{}',interval 14 day),'%Y%m%d')  
     and a.product_category_id=6
     and  a.product_source_id in (101,103)
      and (a.sale_out_cnt is  null OR a.sale_out_cnt<=1)
    and a.mapping_product_level_id >0
    GROUP BY 1,2,3
    order by 1,2,3
)c
on price.product_sku_key=c.product_sku_id and price.product_level_key=c.product_level_id
left join

(
    select a.product_id,a.mapping_product_sku_id as product_sku_id,
    a.mapping_product_level_id as product_level_id,
    avg(a.real_sell_price) as price,
    COUNT(1) AS sale_num,
    count(distinct shop_out_date) as cnt_date,max(a.real_sell_price) as maxprice,min(a.real_sell_price) as minprice
     from product_price_info  a
    left join (
        select product_id,mapping_product_sku_id,
        count(distinct mapping_product_level_id) AS cnt,
        count(distinct product_level_id_2) AS cnt1
        from
        (
        select distinct a.product_id,a.mapping_product_sku_id ,b.level_name as mapping_product_level_name ,
        a.mapping_product_level_id,
        case when substring(b.level_name,1,1) in ('S','A','B','C') 
        THEN a.mapping_product_level_id ELSE null end product_level_id_2 
         from product_price_info a 
         inner join warehouse.dim_product_level b
         on a.mapping_product_level_id = b.level_id
        where a.shop_out_date between DATE_FORMAT(date_sub('{}',interval 41 day),'%Y%m%d')  and DATE_FORMAT(date_sub('{}',interval 21 day),'%Y%m%d') 
        and a.product_category_id=6  and a.mapping_product_level_id>0
         and (a.sale_out_cnt is  null OR a.sale_out_cnt<=1)
        ) a
        GROUP BY 1,2
        having  count(distinct product_level_id_2)>0 and count(distinct mapping_product_level_id)>=0
    )b
    on  a.product_id=b.product_id and a.mapping_product_sku_id=b.mapping_product_sku_id
    
    where a.shop_out_date between DATE_FORMAT(date_sub('{}',interval 41 day),'%Y%m%d')  and DATE_FORMAT(date_sub('{}',interval 21 day),'%Y%m%d')  
     and a.product_category_id=6
    and  a.product_source_id in (101,103)
     and (a.sale_out_cnt is  null OR a.sale_out_cnt<=1)
    and a.mapping_product_level_id >0

    GROUP BY 1,2,3
    order by 1,2,3
)d
on price.product_sku_key=d.product_sku_id and price.product_level_key=d.product_level_id
where price.date=date_add('{}',interval 1 day)
"""
# where price.date=DATE_FORMAT(date_add('{}',interval 0 day),'%Y%m%d')

def get_period_price_fun(date,flag=True):

    if flag:
        q_sql=query_sql.format(date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date,date)
        df=mysql_prediction_processor.load_sql(q_sql)
        process_df=process_settle_Data(df)
        process_df[['product_sku_key', 'product_level_key']] = process_df[['product_sku_key', 'product_level_key']].apply(
            np.int64)
        process_df.to_csv('/data/sunpengwei/tmp/sku2_price_tablet_period_detail.csv', encoding='utf-8-sig')
    else:
        process_df=pd.read_csv('/data/sunpengwei/tmp/sku2_price_tablet_period_detail.csv',index_col=0)
    return process_df

def process_settle_Data(data=None):

    t1 = time.time()
    data['count'] = 0
    data['count_weight'] = 0
    data['sumprice'] = 0
    data['min_week_price']=-1
    data['max_week_price']=sys.maxsize

    for i in range(data.shape[0]):
        min_week_price = sys.maxsize
        max_week_price = -1
        if (data.at[i, 'price_0_7'] > 0):
            data.at[i, 'count'] += data.at[i, 'sale_num_0_7']
            data.at[i, 'count_weight'] += data.at[i, 'sale_num_0_7']*5
            data.at[i, 'sumprice'] += data.at[i, 'price_0_7']*1.0*data.at[i, 'sale_num_0_7']*5
            min_week_price=min(min_week_price,data.at[i, 'price_0_7'])
            max_week_price=max(max_week_price,data.at[i, 'price_0_7'])
        if (data.at[i, 'price_8_14'] > 0):
            data.at[i, 'count'] += data.at[i, 'sale_num_8_14']
            data.at[i, 'count_weight'] += data.at[i, 'sale_num_8_14'] * 3
            data.at[i, 'sumprice'] += data.at[i, 'price_8_14']*0.99*data.at[i, 'sale_num_8_14'] *3
            min_week_price=min(min_week_price,data.at[i, 'price_8_14'])
            max_week_price=max(max_week_price,data.at[i, 'price_8_14'])
        if (data.at[i, 'price_15_21'] > 0):
            data.at[i, 'count'] += data.at[i, 'sale_num_15_21']
            data.at[i, 'count_weight'] += data.at[i, 'sale_num_15_21'] * 1
            data.at[i, 'sumprice'] += data.at[i, 'price_15_21']*0.97*data.at[i, 'sale_num_15_21'] *1
            min_week_price=min(min_week_price,data.at[i, 'price_15_21'])
            max_week_price=max(max_week_price,data.at[i, 'price_15_21'])
        if (data.at[i, 'price_22_42'] > 0):
            data.at[i, 'count'] += data.at[i, 'sale_num_22_42']
            data.at[i, 'count_weight'] += data.at[i, 'sale_num_22_42'] * 1
            data.at[i, 'sumprice'] += data.at[i, 'price_22_42']*0.95*data.at[i, 'sale_num_22_42'] *1
            min_week_price=min(min_week_price,data.at[i, 'price_22_42'])
            max_week_price=max(max_week_price,data.at[i, 'price_22_42'])
        data.at[i,'min_week_price'] = min_week_price
        data.at[i,'max_week_price'] = max_week_price
    data['avgprice'] = data['sumprice'] / data['count_weight']

    logger.info('process max_min_sum use time @{}'.format(time.time() - t1))

    data['thisprice'] = np.nan

    maxint = sys.maxsize

    # 完成如下运算逻辑
    '''
        # =IFERROR(
    #     MIN(SUM(N4*0.98*1, O4*0.99*2, P4*1*3) / COUNT(N4*1, O4*2, P4*3),
    #         IF(ISNUMBER(P4), P4,
    #            IF(ISNUMBER(O4), O4 * 0.99,
    #               IF(ISNUMBER(N4), N4 * 0.98, 0)
    #               )
    #            )
    #         ),
    #     0)
    '''
    for i in range(data.shape[0]):
        p0 = data.at[i, 'avgprice']
        p1 = maxint
        p2 = maxint
        p3 = maxint
        p4 = maxint
        if (pd.isna(p0)):
            p0 = maxint
        if (pd.isna(data.at[i, 'price_0_7'])):
            p1 = maxint
            if (pd.isna(data.at[i, 'price_8_14'])):
                p2 = maxint
                if (pd.isna(data.at[i, 'price_15_21'])):
                    p3 = maxint
                    if (pd.isna(data.at[i, 'price_22_42'])):
                        p4 = maxint
                    else:
                        p4 = data.at[i, 'price_22_42'] * 0.95
                else:
                    p3 = data.at[i, 'price_15_21'] * 0.97
            else:
                p2 = data.at[i, 'price_8_14'] * 0.99
        else:
            p1 = data.at[i, 'price_0_7']

        thisprice = min(p0, p1, p2, p3, p4)
        if (thisprice == maxint):
            thisprice = 0
        data.at[i, 'thisprice'] = thisprice
    data['thisprice'] = data['avgprice']
    logger.info('process period price Data use time @{}'.format(time.time() - t1))
    return data

if __name__=='__main__':
    get_period_price_fun('2021-11-10',flag=True)